/**
 *
 * The MIT License
 *
 * Copyright (c) 2008 Board of Trustees, Leland Stanford Jr. University. All rights reserved.
 * Copyright (c) 2009 Ben Suter. All rights reserved.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:

 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.

 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */
package org.macdadi.core.server;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import org.macdadi.core.client.domain.*;

import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author Ben Suter <ben.suter@gmail.com>
 */
public class DataWriteServiceImpl extends RemoteServiceServlet implements DataWriteService
{
    // TODO: test for SQL injection attacks and block them, i.e. sanitize input

    protected static final String COL_ID = "id";
    protected static final String COL_NAME = "name";
    protected static final String COL_OBJ_ID = "object_id";

    protected transient Connection db;
    protected final String dbPropertiesFile = "database.properties";
    protected final String dbPropertiesDriverClass = "driverclass";
    protected final String dbPropertiesConnectionString = "connectionstring-write";
    /* The MySQL JDBC connector demands read access to system tables when executing stored procedures,
     * unless the "noAccessToProcedureBodies" connection string parameter is activated. This connection
     * parameter means that all stored procedure parameters are sent as string values, roughly.
     * The purpose is to allow the use of a MySQL account that has limited access, namely can ownly
     * execute stored procedures (within the macdadi database).
     */

    public DataWriteServiceImpl()
    {
        super();

        /* Setup the DB connection */
        try {
            Properties dbProps = new Properties();
            dbProps.load(getClass().getResourceAsStream("database.properties"));

            Class.forName(dbProps.getProperty(dbPropertiesDriverClass));
            db = DriverManager.getConnection(dbProps.getProperty(dbPropertiesConnectionString));
        } catch (IOException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);

        } catch (ClassNotFoundException ex) {

            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Option createOption(int project, int author, String name, String authToken)
    {
// checks not needed since SPROC parameter?
//        name = escape(name);
//        if ( ! isSafeValue(name) )
//            return null;

        try {
            StringBuilder call = new StringBuilder();
            call.append("call option_create(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, author);  // p_author
            cs.setString(4, name); // p_name
            cs.registerOutParameter(5, Types.INTEGER);  // p_result_id
            ResultSet r = cs.executeQuery();
            int resultId;
            resultId = cs.getInt(5);
            if (resultId > 0) {
                if (r.next()) {
                    Option o = new Option();
                    o.setId(r.getInt(COL_ID));
                    o.setName(r.getString(COL_NAME));
                    cs.close();
                    return o;
                }
            }
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    // TODO: decide where to enforce permissions, i.e. user is author
    // --> probably to start, only just hide edit controls for the non-author (good enough?)
    public Option updateOption(int project, int option, String name, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call option_update(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, option);  // p_option
            cs.setString(4, name); // p_name
            cs.registerOutParameter(5, Types.BOOLEAN);  // p_success
            ResultSet r = cs.executeQuery();
            boolean success;
            success = cs.getBoolean(5);
            if (success) {
                if (r.next()) {
                    Option o = new Option();
                    o.setId(r.getInt(COL_ID));
                    o.setName(r.getString(COL_NAME));
                    cs.close();
                    return o;
                }
            }
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public boolean removeOption(int project, int option, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call option_remove(?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, option);  // p_option
            cs.registerOutParameter(4, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(4);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    /**
     * Create a new goal, as a child (sub-goal) of the specified parent.
     */
    public Goal createGoal(int project, int parent, String name, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call goal_create(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, parent);  // p_parent
            cs.setString(4, name); // p_name
            cs.registerOutParameter(5, Types.INTEGER);  // p_result_id
            ResultSet r = cs.executeQuery();
            int resultId;
            resultId = cs.getInt(5);
            if (resultId > 0) {
                if (r.next()) {
                    Goal g = new Goal();
                    /* Note: This goal does not have valid parent or child refs. */
                    g.setId(r.getInt(COL_ID));
                    g.setName(r.getString(COL_NAME));
                    cs.close();
                    return g;
                }
            }
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public Goal updateGoal(int project, int goal, String name, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call goal_update(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, goal);  // p_goal
            cs.setString(4, name); // p_name
            cs.registerOutParameter(5, Types.BOOLEAN);  // p_success
            ResultSet r = cs.executeQuery();
            boolean success;
            success = cs.getBoolean(5);
            if (success) {
                if (r.next()) {
                    Goal g = new Goal();
                    g.setId(r.getInt(COL_ID));
                    g.setName(r.getString(COL_NAME));
                    cs.close();
                    return g;
                }
            }
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public boolean removeGoal(int project, int goal, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call goal_remove(?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, goal);  // p_goal
            cs.registerOutParameter(4, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(4);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public StakeholderGroup createStakeholderGroup(int project, int parent, String name, String authToken)
    {
        // TODO: get rid of this and fix code below to retrieve parent, the DB SPROC needs only
        // small update to retrieve parent details, or leave as is (see goal_create SPROC)?
        if (parent >= 0)
            throw new IllegalArgumentException("FOR NOW, nesting is not enabled yet! So parent should be -1");

        try {
            StringBuilder call = new StringBuilder();
            call.append("call stakeholder_group_create(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, parent);  // p_parent
            cs.setString(4, name); // p_name
            cs.registerOutParameter(5, Types.INTEGER);  // p_result_id
            ResultSet r = cs.executeQuery();
            int resultId;
            resultId = cs.getInt(5);
            if (resultId > 0) {
                if (r.next()) {
                    StakeholderGroup sg = new StakeholderGroup();
                    /* Note: This group does not have valid parent or child refs
                     * Since it was just created, it has no members.
                     * For now, we are not YET nesting groups within groups,
                     * so the parent will be -1.
                     */
                    // TODO: create a valid parent reference once nesting is enabled
                    sg.setId(r.getInt(COL_ID));
                    sg.setName(r.getString(COL_NAME));
                    cs.close();
                    return sg;
                }
            }
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public StakeholderGroup updateStakeholderGroup(int project, int stakeholderGroup, String name, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call stakeholder_group_update(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, stakeholderGroup);  // p_group
            cs.setString(4, name); // p_name
            cs.registerOutParameter(5, Types.BOOLEAN);  // p_success
            ResultSet r = cs.executeQuery();
            boolean success;
            success = cs.getBoolean(5);
            if (success) {
                // TODO: be aware, this StakeholderGroup does not contain any
                // members as returned from this method. That is generally okay, as
                // long as this method is only used to trigger the changes, and then
                // the full list is retrieved properly (this is the pattern we generally
                // are using). Otherwise, see code below this that expects a different
                // record set to be returned from the SPROC
                if (r.next()) {
                    StakeholderGroup g = new StakeholderGroup();
                    g.setId(r.getInt(COL_ID));
                    g.setName(r.getString(COL_NAME));
                    cs.close();
                    return g;
                }
            }
            // the following code would return the group complete with member references,
            // but requires changes to the final SELECT in the SPROC
//            if ( success ) {
//                StakeholderGroup sg = null;
//                int userId;
//                User u;
//                while (r.next()) {
//                    if ( sg == null ) {
//                        sg = new StakeholderGroup();
//                        sg.setId(r.getInt("sg_id"));
//                        sg.setName(r.getString("sg_name"));
//                    }
//
//                    userId = r.getInt("user_id");
//                    if (userId > 0) {
//                        u = new User();
//                        u.setId(userId);
//                        u.setName(r.getString("user_name"));
//                        sg.addMember(u);
//                    }
//                }
//                cs.close();
//                return sg;
//            }
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public boolean removeStakeholderGroup(int project, int stakeholderGroup, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call stakeholder_group_remove(?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, stakeholderGroup);  // p_group
            cs.registerOutParameter(4, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(4);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean addMemberToStakeholderGroup(int project, int stakeholderGroup, int userId, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call stakeholder_group_add_member(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, stakeholderGroup);  // p_group
            cs.setInt(4, userId); // p_user
            cs.registerOutParameter(5, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(5);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean removeMemberFromStakeholderGroup(int project, int stakeholderGroup, int userId, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call stakeholder_group_remove_member(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, stakeholderGroup);  // p_group
            cs.setInt(4, userId); // p_user
            cs.registerOutParameter(5, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(5);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean updatePreference(int project, int goal, int userId, int value, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call preference_update(?,?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, goal);  // p_goal
            cs.setInt(4, userId); // p_user
            cs.setInt(5, value); // p_value
            cs.registerOutParameter(6, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(6);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean updateImpact(int project, int goal, int option, int value, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call impact_update(?,?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, goal);  // p_goal
            cs.setInt(4, option); // p_option
            cs.setInt(5, value); // p_value
            cs.registerOutParameter(6, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(6);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean addDesigner(int project, int userId, String authToken)
    {
        return addRoleMember(project, userId, "designer", authToken);
    }

    public boolean removeDesigner(int project, int userId, String authToken)
    {
        return removeRoleMember(project, userId, "designer", authToken);
    }

    public boolean addDecisionMaker(int project, int userId, String authToken)
    {
        return addRoleMember(project, userId, "decision-maker", authToken);
    }

    public boolean removeDecisionMaker(int project, int userId, String authToken)
    {
        return removeRoleMember(project, userId, "decision-maker", authToken);
    }

    public boolean addProjectMember(int project, int userId, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call project_member_add(?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, userId);  // p_user
            cs.registerOutParameter(4, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(4);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean removeProjectMember(int project, int userId, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call project_member_remove(?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, userId);  // p_user
            cs.registerOutParameter(4, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(4);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public ProjectInfo createProject(String name, String urlPath, int sourceProject, String authToken)
    {
        // TODO: implement copying from source project, see below
        if ( sourceProject > 0 )
            throw new IllegalArgumentException("Copying from source projects is not yet supported");
        
        try {
            StringBuilder call = new StringBuilder();
            call.append("call project_create(?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setString(2, name); // p_name
            cs.setString(3, urlPath);  // p_url_path
            cs.registerOutParameter(4, Types.INTEGER);  // p_result_id
            ResultSet r = cs.executeQuery();
            int resultId;
            resultId = cs.getInt(4);
            if (resultId > 0) {
                // TODO: if sourceProject specified, copy members, roles, stakeholders, goals, and options
                if (r.next()) {
                    ProjectInfo p = new ProjectInfo();
                    p.setId(resultId);
                    p.setName(r.getString(COL_NAME));
                    p.setPublic(r.getBoolean(3));
                    p.setUrlPath(r.getString(4));
                    p.setAllowCopy(r.getBoolean(5));
                    cs.close();
                    return p;
                }
            }
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public boolean createAccount(String email, String password, String name)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call admin_account_create(?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, email); // p_email
            cs.setString(2, password); // p_password
            cs.setString(3, name); // p_name
            cs.registerOutParameter(4, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(4);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    protected boolean addRoleMember(int project, int userId, String role, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call role_member_add(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, userId);  // p_user
            cs.setString(4, role); // p_role_name
            cs.registerOutParameter(5, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(5);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    protected boolean removeRoleMember(int project, int userId, String role, String authToken)
    {
        try {
            StringBuilder call = new StringBuilder();
            call.append("call role_member_remove(?,?,?,?,?)");
            CallableStatement cs = db.prepareCall(call.toString());
            cs.setString(1, authToken); // p_token
            cs.setInt(2, project); // p_project
            cs.setInt(3, userId);  // p_user
            cs.setString(4, role); // p_role_name
            cs.registerOutParameter(5, Types.BOOLEAN);  // p_success
            cs.executeQuery();
            boolean success;
            success = cs.getBoolean(5);
            cs.close();
            return success;
        } catch (SQLException ex) {
            Logger.getLogger(DataWriteServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public static String escape(String value)
    {
        return value.replace("'", "''");
    }

    // TODO: block as much SQL injection as possible
    public static boolean isSafeValue(String value)
    {
        return !value.contains(";");
    }
}